from collections import defaultdict,Counter
from tool import tms,getTimeStr,getsmtpro
from api import fix_excel_xml

import os,json,openpyxl,logging,math

def dc_ty_pro(itemids):
        smtys={
            '标题':'yntitleRcode',
            '产品ID':'itemid',
            '详细参数':'attributes',
            'SKU图片网址':'skuImg',
            '价格':'skuPirce',
            'SKUID':'skuid',
            '描述':'pdec'
        }
        zjys={
            'Option for Variation 1':'sku1',
            'Option for Variation 2':'sku2',
        }

 

        phl=420
        pbs=1
        mbid=11105
        mbpath='D:/mmmm/document4/阿敏福州广告测品模板.xlsx'
        rys=tms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))
        print(f'正在获取模板:{mbid},{len(itemids)} 个数据...')
        wstr=','.join([f"'{_}'" for _ in itemids])
        sqlstr=f'select * from proshopeeph where itemid in ({wstr})'
        rp=tms.ExecQuerydict(sqlstr)
        dcmr=f'导出结果(通用模版)_{getTimeStr()}'
        os.mkdir(dcmr)
        smt_rp=getsmtpro(rp)
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        row=7
        pskudatas=[]
        for pii_lsit in smt_rp:
            pskudatas.extend(pii_lsit)

        for pro in pskudatas:
            pro['price']=int(phl*pro['price']*pbs)
            pro['skuPirce']=int(phl*pro['skuPirce']*pbs)
            allimgs=[pro['image']]
            if pro['images']:
                try:
                    allimgs.extend(json.loads(pro['images']))
                except Exception as e:
                    pass

            rol=1
            try:
                for ry in rys:
                    mbcol=ry[1]
                    smtcol=ry[2]
                    gd=ry[4]

                    if smtcol=='SKU图片网址':
                        rol+=1
                        continue
                    
                    if '无' not in smtcol and smtcol:
                        if smtcol=='SKU名称':
                            if zjys.get(mbcol):
                                ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                    ws.cell(row=row,column=rol).value='style'

                        elif smtcol=='主图网址':
                            if len(allimgs)>0:
                                ws.cell(row=row,column=rol).value=allimgs.pop(0)

                        else:
                            ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                    
                    else:
                        ws.cell(row=row,column=rol).value=gd
                        if gd=='size' and not pro['sku2']:
                            ws.cell(row=row,column=rol).value=''

                    rol+=1
                row+=1
            except Exception as e:
                logging.info(f'第{row}行,第{rol}列填写错误 =>{e}')
        ex_path=f'{dcmr}/广告数据.xlsx'
        wb.save(ex_path)
        print(f'模版:{mbid},成功导出{len(smt_rp)}个产品,到:《{ex_path}》')

        return len(smt_rp)

def alter_1688sku(rp):
    min_price=159000
    
    for item in rp:

        if not item['pdec']:
            item['pdec'] = item['yntitle']

        if item['pdec'] and len(item['pdec']) > 2500:
            item['pdec'] = item['pdec'][:2500]
        if item['sku_price']<min_price:
            item['sku_price']=min_price
        

    # 处理 sku1 和 sku2 重复的情况
    rp_dict = {}
    for r in rp:
        item_id = r['itemid']
        if rp_dict.get(item_id):
            rp_dict[item_id].append(r)
        else:
            rp_dict[item_id] = [r]
        
    
    for itemid,pskulist in rp_dict.items():
        cn_sku1_to_ynsku1={}
        cn_sku2_to_ynsku2={}

        for pitem in pskulist:
            if pitem['cn_sku1']:
                ynsku = pitem['sku1']
                cn_sku1_to_ynsku1[pitem['cn_sku1']] = ynsku[:17]
            if pitem['cn_sku2']:
                ynsku = pitem['sku2']
                cn_sku2_to_ynsku2[pitem['cn_sku2']] = ynsku[:17]

        for zddd in [cn_sku1_to_ynsku1,cn_sku2_to_ynsku2]:

            ynsku_dict=defaultdict(list)
            for cnsku,ynsku in zddd.items():
                ynsku_dict[ynsku].append((cnsku,ynsku))
            
            for rp_ynsku,skulll in ynsku_dict.items():

                if len(skulll)>1:
                    for jjj in range(len(skulll)):
                        if jjj==0:
                            continue
                        zw_sku=skulll[jjj][0]
                        zddd[zw_sku]=f'{rp_ynsku}_{jjj}'

        piii=0
        min_p=min(pskulist, key=lambda x: x["sku_price"])['sku_price']
        max_p=max(pskulist, key=lambda x: x["sku_price"])['sku_price']
        if max_p/min_p>7:
            min_p=math.ceil(max_p/7)
        for pitem in pskulist:
            if pitem['cn_sku1']:
                pitem['sku1']=cn_sku1_to_ynsku1[pitem['cn_sku1']]
            if pitem['cn_sku2']:
                pitem['sku2']=cn_sku2_to_ynsku2[pitem['cn_sku2']]

            if not pitem['sku1_img']:
                if piii<len(pskulist)-1:
                    pitem['sku1_img']=pskulist[piii+1]['sku1_img']
            if pitem['sku_price']<min_p:
                pitem['sku_price']=min_p
            piii+=1


    final_result = []
    for item_id, records in rp_dict.items():
        grouped_by_sku1 = defaultdict(list)
        cur_sku2s=defaultdict(set)
        for record in records:
            grouped_by_sku1[record['sku1']].append(record)
            if not record['sku2']:
                record['sku2']=''
            cur_sku2s[record['sku1']].add(record['sku2'])
        current_count = 0
        item_group = []
        #print(cur_sku2s.values())
        set_counts = Counter(tuple(sorted(s)) for s in cur_sku2s.values())
        most_common_set, count = set_counts.most_common(1)[0]
        for sku1, sku_records in grouped_by_sku1.items():
            if cur_sku2s[sku1]==set(most_common_set):
                if current_count + len(sku_records) <= 80:
                    item_group.extend(sku_records)
                    current_count += len(sku_records)
                else:
                    break
        final_result.append(item_group)
    #final_result=list(rp_dict.values())
    return final_result

def alter2_1688sku(rp):
    min_price=159000
    
    for item in rp:

        if not item['pdec']:
            item['pdec'] = item['yntitle']

        if item['pdec'] and len(item['pdec']) > 2500:
            item['pdec'] = item['pdec'][:2500]
        if item['sku_price']<min_price:
            item['sku_price']=min_price
        


    rp_dict = defaultdict(list)
    for r in rp:
        item_id = r['itemid']
        rp_dict[item_id].append(r)
    
    for itemid,pskulist in rp_dict.items():

        piii=0
        min_p=min(pskulist, key=lambda x: x["sku_price"])['sku_price']
        max_p=max(pskulist, key=lambda x: x["sku_price"])['sku_price']
        if max_p/min_p>7:
            min_p=math.ceil(max_p/7)
        for pitem in pskulist:

            if not pitem['sku1_img']:
                if piii<len(pskulist)-1:
                    pitem['sku1_img']=pskulist[piii+1]['sku1_img']
            if pitem['sku_price']<min_p:
                pitem['sku_price']=min_p
            piii+=1


    final_result = []
    for item_id, records in rp_dict.items():
        grouped_by_sku1 = defaultdict(list)
        cur_sku2s=defaultdict(set)
        for record in records:
            grouped_by_sku1[record['sku1']].append(record)
            if not record['sku2']:
                record['sku2']=''
            cur_sku2s[record['sku1']].add(record['sku2'])
        current_count = 0
        item_group = []
        #print(cur_sku2s.values())
        set_counts = Counter(tuple(sorted(s)) for s in cur_sku2s.values())
        most_common_set, count = set_counts.most_common(1)[0]
        for sku1, sku_records in grouped_by_sku1.items():
            if cur_sku2s[sku1]==set(most_common_set):
                if current_count + len(sku_records) <= 80:
                    item_group.extend(sku_records)
                    current_count += len(sku_records)
                else:
                    break
        final_result.append(item_group)
    #final_result=list(rp_dict.values())
    return final_result

def s1688_to_ynpro(pc=200,fc=1,sjbm='C8888'):
    total_pcount=pc*fc
    lname_s1688_dict={
        'Product Name':'yntitle',
        'Product Description':'pdec',
        'Parent SKU':'itemid',
        'Variation Integration No.':'itemid',
        'Variation Name1':'sku1_name',
        'Variation Name2':'sku2_name',
        'Option for Variation 1':'sku1',
        'Option for Variation 2':'sku2',
        'Price':'sku_price',
        'Image per Variation':'sku1_img',
        'SKU':'skuid',
        'Cover image':'image',
        'Item Image 1':('images',0),
        'Item Image 2':('images',1),
        'Item Image 3':('images',2),
        'Item Image 4':('images',3),
        'Item Image 5':('images',4),
        'Item Image 6':('images',5),
        'Item Image 7':('images',6),
        'Item Image 8':('images',7)
        
    }

    lname_gd_dict={
        'Stock':'100',
        'Weight':'10',
        'Reguler (Cashless)':'On'
    }


    sqlstr=f'''SELECT 
                sp.itemid,
                sp.yntitle + ' |' + sp.random_code + '|' AS yntitle,
                sp.sku1_name,
                sp.sku2_name,
                sp.image,
                sp.images,
                sk.sku1_img,
                sk.sku1 AS cn_sku1,
                sk.sku2 AS cn_sku2,
                sk.ynsku1 AS sku1,
                sk.ynsku2 AS sku2,
                sk.skuid,
                sku_price AS cn_price,
                cast(sk.sku_price * 1.7 * 2250 as int) AS sku_price,
                sp.yndes AS pdec
            FROM 
                (SELECT TOP {total_pcount} itemid FROM S1688Pro WHERE is_yn_ad = 0 AND is_yn_dc = 0 and is_fy_sku=1 and SJBM='{sjbm}' 
                and is_yntit=1
                ) AS temp
            JOIN 
                S1688Pro sp ON sp.itemid = temp.itemid
            JOIN 
                S1688ProSKU sk ON sp.itemid = sk.itemid;
            '''
    
    sqlstr2=f'''SELECT 
                sp.itemid,
                sp.yntitle + ' |' + sp.random_code + '|' AS yntitle,
                sp.sku1_name,
                sp.sku2_name,
                sp.image,
                sp.images,
                sk.sku1_img,
                sk.sku1 AS cn_sku1,
                sk.sku2 AS cn_sku2,
                sk.ynsku1 AS sku1,
                sk.ynsku2 AS sku2,
                sk.skuid,
                cast(sk.sku_price * 1.7 * 2250 as int) AS sku_price,
                sku_price AS cn_price,
                sp.yndes AS pdec
            FROM 
                (SELECT TOP {total_pcount} itemid FROM S1688Pro WHERE random_code in ('7V3AK5JX')) AS temp
            JOIN 
                S1688Pro sp ON sp.itemid = temp.itemid
            JOIN 
                S1688ProSKU sk ON sp.itemid = sk.itemid;
            '''

    mbpath='D:/mmmm/document4/阿敏福州广告测品模板.xlsx'
    rp=tms.ExecQuerydict(sqlstr2)
    # lname_gd_dict['Stock']=0
    # rp=tms.ExecQuerydict(sqlstr2)
    dcmr=f'1688导出/{sjbm}_1688TOYN_{getTimeStr()}'
    os.mkdir(dcmr)
    smt_rp=alter_1688sku(rp)

    for fj in range(fc):
        dc_itemids=[]
        cur_smt_rp=smt_rp[fj*pc:(fj+1)*pc]
        if len(cur_smt_rp)==0:
            break
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        mb_tits=[ws.cell(3,col).value for col in range(1,ws.max_column+1)]
        row=7
        pskudatas=[]
        for pii_lsit in cur_smt_rp:
            pskudatas.extend(pii_lsit)
            dc_itemids.append(pii_lsit[0]['itemid'])
        allimgs=[]
        pskudatas.sort(key=lambda x: (x['itemid'],x['sku1'], x['sku2']))
        for pro in pskudatas:
            if pro['images']:
                try:
                    allimgs=json.loads(pro['images'])
                except Exception as e:
                    pass
            
            try:
                for tit_col_ind in range(len(mb_tits)):
                    rol=tit_col_ind+1
                    lname=mb_tits[tit_col_ind]


                    if lname in lname_s1688_dict:
                        czddd=lname_s1688_dict[lname]
                        if isinstance(czddd,str):
                            ws.cell(row=row,column=rol).value=pro[czddd]
                        else:
                            ccc_ind=czddd[1]
                            if ccc_ind<len(allimgs):
                                ws.cell(row=row,column=rol).value=allimgs[ccc_ind]

                    elif lname in lname_gd_dict:
                        if lname=='Stock':
                            if pro['cn_price']<15 or pro['cn_price']>60:
                                ws.cell(row=row,column=rol).value=0
                            else:
                                ws.cell(row=row,column=rol).value=lname_gd_dict[lname]
                        
                        else:
                            ws.cell(row=row,column=rol).value=lname_gd_dict[lname]

                row+=1
            except Exception as e:
                print(f'第{row}行,第{rol}列填写错误 =>{e}')
        ex_path=f'{dcmr}/{sjbm}_第{fj+1}份{len(cur_smt_rp)}个_1688TOYN.xlsx'
        wb.save(ex_path)
        wstr=','.join([f"'{dc_itemid}'" for dc_itemid in dc_itemids])
        aff_dc=tms.ExecNoQuery(f'update S1688Pro set is_yn_dc=1 where itemid in ({wstr})')
        print(f'虾皮通用模版,第 {fj+1} 份文件成功导出{len(cur_smt_rp)}个产品,到:《{ex_path}》,标记 {aff_dc} 个状态is_yn_dc=1 ')

    return len(smt_rp)

def reset_1688_dc(dc_dir):

    execel_files=[os.path.join(dc_dir,file_name) for file_name in os.listdir(dc_dir)]
    for excel_name in execel_files:
        wb=openpyxl.load_workbook(excel_name)
        ws=wb['Template']
        tits=[ws.cell(3,ccc).value.strip() for ccc in range(1,ws.max_column)]
        itemid_col=tits.index('Parent SKU')+1
        itemid_set=set()
        for rr in range(7,ws.max_row+1):
            cur_itemid=ws.cell(rr,itemid_col).value
            if cur_itemid:
                itemid_set.add(cur_itemid.strip())
        
        print(f'文件:《{excel_name}》共{len(itemid_set)}个产品,开始回溯状态')
        wstr=','.join([f"'{itemid}'" for itemid in itemid_set])
        aff=tms.ExecNoQuery(f'update S1688Pro set is_yn_dc=0 where is_yn_dc=1 and itemid in ({wstr})')
        print(f'文件:《{excel_name}》,成功回溯 {aff} 个产品状态')

def ph_to_ynpro(pc=200,fc=1):

    total_pcount=pc*fc
    lname_s1688_dict={
        'Product Name':'yntitle',
        'Product Description':'pdec',
        'Parent SKU':'itemid',
        'Variation Integration No.':'itemid',
        'Variation Name2':'sku2_name',
        'Option for Variation 1':'sku1',
        'Option for Variation 2':'sku2',
        'Price':'sku_price',
        'Image per Variation':'sku1_img',
        'SKU':'skuid',
        'Cover image':'image',
        'Item Image 1':('images',0),
        'Item Image 2':('images',1),
        'Item Image 3':('images',2),
        'Item Image 4':('images',3),
        'Item Image 5':('images',4),
        'Item Image 6':('images',5),
        'Item Image 7':('images',6),
        'Item Image 8':('images',7)
        
    }

    lname_gd_dict={
        'Variation Name1':'color',
        'Stock':'100',
        'Weight':'10',
        'Reguler (Cashless)':'On'
    }


    sqlstr=f'''SELECT 
                sp.itemid,
                sp.yntitle + ' |' + sp.random_code + '|' AS yntitle,
                sp.sku2_name,
                sp.image,
                sp.images,
                sk.sku1_img,
                sk.sku1,
                sk.sku2,
                sk.skuid,
				sp.pdec,
                cast(((sk.sku_price/sp.price)*sp.Y_1688price * 1.8 * 2250) as int) AS sku_price
            FROM 
                (SELECT top {total_pcount} itemid FROM ShopeePHADPro WHERE is_yn_dc = 0 and Y_1688price is not null and is_cj=1 and yntitle is not null) AS temp
            JOIN 
                ShopeePHADPro sp ON sp.itemid = temp.itemid
            JOIN 
                ShopeePHADProSKU sk ON sp.itemid = sk.itemid;
            '''

    mbpath='wzmmb.xlsx'

    rp=tms.ExecQuerydict(sqlstr)
    dcmr=f'菲律宾虾皮导出/菲律宾虾皮转印尼广告产品_{getTimeStr()}'
    os.mkdir(dcmr)
    smt_rp=getsmtpro(rp)

    for fj in range(fc):
        dc_itemids=[]
        cur_smt_rp=smt_rp[fj*pc:(fj+1)*pc]
        if len(cur_smt_rp)==0:
            break
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        mb_tits=[ws.cell(3,col).value for col in range(1,ws.max_column+1)]
        row=7
        pskudatas=[]
        for pii_lsit in cur_smt_rp:
            pskudatas.extend(pii_lsit)
            dc_itemids.append(pii_lsit[0]['itemid'])
        allimgs=[]

        for pro in pskudatas:
            if pro['images']:
                try:
                    allimgs=json.loads(pro['images'])
                except Exception as e:
                    pass
            if not pro['sku1']:
                pro['sku1']='one style'

            try:
                for tit_col_ind in range(len(mb_tits)):
                    rol=tit_col_ind+1
                    lname=mb_tits[tit_col_ind]


                    if lname in lname_s1688_dict:
                        czddd=lname_s1688_dict[lname]
                        if isinstance(czddd,str):
                            ws.cell(row=row,column=rol).value=pro[czddd]
                        else:
                            ccc_ind=czddd[1]
                            if ccc_ind<len(allimgs):
                                ws.cell(row=row,column=rol).value=allimgs[ccc_ind]

                    elif lname in lname_gd_dict:
                        ws.cell(row=row,column=rol).value=lname_gd_dict[lname]

                row+=1
            except Exception as e:
                print(f'第{row}行,第{rol}列填写错误 =>{e}')
        ex_path=f'{dcmr}/第{fj+1}份{len(cur_smt_rp)}个_菲律宾转印尼广告产品.xlsx'
        wb.save(ex_path)
        wstr=','.join([f"'{dc_itemid}'" for dc_itemid in dc_itemids])
        aff_dc=tms.ExecNoQuery(f'update ShopeePHADPro set is_yn_dc=1 where itemid in ({wstr})')
        print(f'虾皮通用模版,第 {fj+1} 份文件成功导出{len(cur_smt_rp)}个产品,到:《{ex_path}》,标记 {aff_dc} 个状态is_yn_dc=1 ')

    return len(smt_rp)

def reset_ph_dc(dc_dir):

    execel_files=[os.path.join(dc_dir,file_name) for file_name in os.listdir(dc_dir)]
    for excel_name in execel_files:
        wb=openpyxl.load_workbook(excel_name)
        ws=wb['Template']
        tits=[ws.cell(3,ccc).value.strip() for ccc in range(1,ws.max_column)]
        itemid_col=tits.index('Parent SKU')+1
        itemid_set=set()
        for rr in range(7,ws.max_row+1):
            cur_itemid=ws.cell(rr,itemid_col).value
            if cur_itemid:
                itemid_set.add(cur_itemid.strip())
        
        print(f'文件:《{excel_name}》共{len(itemid_set)}个产品,开始回溯状态')
        wstr=','.join([f"'{itemid}'" for itemid in itemid_set])
        aff=tms.ExecNoQuery(f'update ShopeePHADPro set is_yn_dc=0 where is_yn_dc=1 and itemid in ({wstr})')
        print(f'文件:《{excel_name}》,成功回溯 {aff} 个产品状态')

def sql_to_excel(tb_name,row_count=None,cn_tb_name='',dir_path='表格导出'):
    cur_time_str=getTimeStr()
    ys_path=f'TABLEJSON/{tb_name}.json'
    ys_dict=None
    if os.path.exists(ys_path):
        with open(ys_path,'r',encoding='utf-8') as f:
            ys_dict=json.load(f)
    if row_count:
        sql_str=f'select top {row_count} * from {tb_name}'
    else:
        sql_str=f'select * from {tb_name}'
        #sql_str=f"select * from keyword_1688 where keyword_type='cpc优秀'"
    rs=tms.ExecQuerydict(sql_str)
    with open('TABLEJSON/TABLE.json','r',encoding='utf-8') as f:
        tb_name_map=json.load(f)
    
    if tb_name_map.get(tb_name):
        cn_tb_name=tb_name_map[tb_name]

    if len(rs)>0:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        en_tit_line=list(rs[0].keys())
        ws.append(en_tit_line)
        if ys_dict:
            cn_tit_line=[ ys_dict[en_tit] if ys_dict.get(en_tit) else en_tit for en_tit in en_tit_line]
            ws.append(cn_tit_line)
        
        for r in rs:
            ws.append(list(r.values()))
        
        wb.save(f'{dir_path}/{tb_name}({cn_tb_name})_{cur_time_str}.xlsx')
    
    else:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        en_tit_line=tms.GetCol(tb_name)
        ws.append(en_tit_line)
        if ys_dict:
            try:
                cn_tit_line=[ ys_dict[en_tit] if ys_dict.get(en_tit) else en_tit for en_tit in en_tit_line]
                ws.append(cn_tit_line)
            except Exception as e:
                print(e)
        
        wb.save(f'{dir_path}/{tb_name}({cn_tb_name})_{cur_time_str}.xlsx')


def sql_to_excel2(tb_name,row_count=None,cn_tb_name='',dir_path='表格导出'):
    cur_time_str=getTimeStr()
    ys_dict=tms.get_col_des_from_procedure(tb_name)

    if row_count:
        sql_str=f'select top {row_count} * from {tb_name}'
    else:
        sql_str=f'select * from {tb_name}'
        #sql_str=f"select * from ShopeeProPerformance where shop_usage='大数据二筛ATC' and dtype='三十天'"
    rs=tms.ExecQuerydict(sql_str)
    with open('TABLEJSON/TABLE.json','r',encoding='utf-8') as f:
        tb_name_map=json.load(f)
    
    if tb_name_map.get(tb_name):
        cn_tb_name=tb_name_map[tb_name]

    if len(rs)>0:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        en_tit_line=list(rs[0].keys())
        ws.append(en_tit_line)
        if ys_dict:
            cn_tit_line=[ ys_dict[en_tit] if ys_dict.get(en_tit) else en_tit for en_tit in en_tit_line]
            ws.append(cn_tit_line)
        
        for r in rs:
            ws.append(list(r.values()))
        
        wb.save(f'{dir_path}/{tb_name}({cn_tb_name})_{cur_time_str}.xlsx')
    
    else:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        en_tit_line=tms.GetCol(tb_name)
        ws.append(en_tit_line)
        if ys_dict:
            try:
                cn_tit_line=[ ys_dict[en_tit] if ys_dict.get(en_tit) else en_tit for en_tit in en_tit_line]
                ws.append(cn_tit_line)
            except Exception as e:
                print(e)
        
        wb.save(f'{dir_path}/{tb_name}({cn_tb_name})_{cur_time_str}.xlsx')



sql_to_excel2('ShopeeProPerformance')




